﻿/********************************************************************************************
 * 文件名称:	DBPersistenceBase.cs
 * 设计人员:	(yanxianghui@gmail.com)
 * 设计时间:	2009-03-16
 * 功能描述:	将实体持久化到数据库的基本操作
 * 注意事项:	
 * 版权所有:	Copyright (c) 2009, Fujian SIRC
 * 修改记录: 	修改时间		人员		修改备注
 *				    ----------		------		-------------------------------------------------
 *
 ********************************************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Data.SqlClient;
using System.Data;

namespace Shanfree.Framework.Utility
{
    /// <summary>
    /// 将实体持久化到数据库的基本操作
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class DBPersistenceBase<T> : IDBPersistenceBase<T> where T : new()
    {
        /// <summary>
        /// 写入扩展属性的操作，实体在不同的应用场合，需要加载的扩展属性也不同
        /// </summary>
        /// <param name="reader">数据库读取器</param>
        /// <param name="t">业务实体</param>
        /// <returns>加载了扩展属性的业务实体</returns>
        protected delegate T WriteExtendPropertyDelegate(SqlDataReader reader, T t);

        /// <summary>
        /// 数据库连接串，必须在派生类中重写
        /// </summary>
        protected virtual string ConnectionString
        {
            get
            {
                throw new NotSupportedException("连接串必须在派生类中重写。");
            }
        }

        /// <summary>
        /// 根据主键获取记录
        /// </summary>
        /// <param name="id">主键</param>
        /// <returns>实体</returns>
        public virtual T GetByID(string id)
        {
            // 表名
            String className = GetViewName(typeof(T));
            // 选取单条记录的sql
            string SQL_SELECT_ID = string.Format(@"SELECT top 1 *
                                                                                  FROM dbo.[{0}]
                                                                                  WHERE ID = @ID", className);
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@ID", id);
            SqlDataReader reader = SqlHelper.ExecuteReader(this.ConnectionString, CommandType.Text, SQL_SELECT_ID, param);
            IList<T> list = GetListFromReader(reader);
            if (list.Count == 0)
            {
                return default(T);
            }
            return list[0];
        }

        #region GetCustomerListByParamList
        /// <summary>
        /// 根据参数执行sql返回自定义列表
        /// </summary>
        /// <param name="connectionString">连接串</param>
        /// <param name="sql">未分页的sql</param>
        /// <param name="param">参数列表</param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        protected CustomList<T> GetCustomerListByParamList(string connectionString, string sql, SqlParameter[] param, int pageSize, int pageIndex)
        {
            string pagedSQL = CommonHelper.GetPagedSQL(sql, pageSize, pageIndex);
            SqlDataReader reader = SqlHelper.ExecuteReader(connectionString, CommandType.Text, pagedSQL, param);
            IList<T> list = GetListFromReader(reader);
            CustomList<T> cList = PageAmoutHelper<T>.GetCutomList(connectionString, sql, param, pageSize, list);
            return cList;
        }
        /// <summary>
        /// 根据参数执行sql返回自定义列表
        /// </summary>
        /// <param name="connectionString">连接串</param>
        /// <param name="sql">未分页的sql</param>
        /// <param name="param">参数列表</param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        protected CustomList<T> GetCustomerListByParamList(string connectionString, string sql, int pageSize, int pageIndex)
        {
            string pagedSQL = CommonHelper.GetPagedSQL(sql, pageSize, pageIndex);
            SqlDataReader reader = SqlHelper.ExecuteReader(connectionString, CommandType.Text, pagedSQL);
            IList<T> list = GetListFromReader(reader);
            CustomList<T> cList = PageAmoutHelper<T>.GetCutomList(connectionString, sql, pageSize, list);
            return cList;
        }

        #endregion

        #region GetListFromReader
        /// <summary>
        /// 从数据库获取实体列表
        /// </summary>
        /// <param name="reader">数据库记录集游标</param>
        /// <param name="del">写入实体扩展属性的方法</param>
        /// <returns>实体列表</returns>
        protected IList<T> GetListFromReader(SqlDataReader reader, WriteExtendPropertyDelegate del)
        {
            IList<T> ilist = new List<T>();
            try
            {
                Type type = typeof(T);
                // 所有属性
                PropertyInfo[] proList = type.GetProperties();
                while (reader.Read())
                {
                    T t = new T();
                    // 写入扩展属性
                    t = del.Invoke(reader, t);
                    foreach (PropertyInfo item in proList)
                    {
                        object[] attr = item.GetCustomAttributes(typeof(PersistenceAttribute), true);
                        // 需要持久化的字段才写入数据库
                        if (attr != null && attr.Length > 0)
                        {
                            PersistenceAttribute pAttr = attr[0] as PersistenceAttribute;
                            string colName = pAttr.ColumnName;
                            // 映射字段为空或为扩展字段则不读取
                            if (string.IsNullOrEmpty(colName) || pAttr.IsExtend == true)
                            {
                                continue;
                            }
                            if (reader[colName] == DBNull.Value)
                            {
                                // 空值不赋值
                                continue;
                            }
                            object proValue = Convert.ChangeType(reader[colName], item.PropertyType);
                            item.SetValue(t, proValue, null);
                        }
                    }
                    ilist.Add(t);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                reader.Close();
            }
            return ilist;
        }

        /// <summary>
        /// 从数据库获取实体列表
        /// </summary>
        /// <param name="reader">数据库记录集游标</param>
        /// <returns>实体列表</returns>
        protected IList<T> GetListFromReader(SqlDataReader reader)
        {
            WriteExtendPropertyDelegate del = new WriteExtendPropertyDelegate(WriteExtendProperty);
            return GetListFromReader(reader, del);
        }

        /// <summary>
        /// 写入扩展属性
        /// </summary>
        /// <param name="reader">数据库游标</param>
        /// <param name="t">实体对象</param>
        /// <returns></returns>
        protected virtual T WriteExtendProperty(SqlDataReader reader, T t)
        {
            return t;
        }
        #endregion

        #region 私有函数
        private PersistenceAttribute GetPersistenceAttribute(MemberInfo mInfo)
        {
            string dbName = string.Empty;
            object[] attr = mInfo.GetCustomAttributes(typeof(PersistenceAttribute), true);
            // 需要持久化的字段才写入数据库
            if (attr != null && attr.Length > 0)
            {
                PersistenceAttribute pAttr = attr[0] as PersistenceAttribute;
                return pAttr;
            }
            return new PersistenceAttribute();
        }
        /// <summary>
        /// 获取表名，删除，更新用
        /// </summary>
        /// <param name="type">实体类型</param>
        /// <returns>对应数据库表名</returns>
        private string GetTableName(Type type)
        {
            // 表名
            String className = GetPersistenceAttribute(type).ColumnName;
            if (string.IsNullOrEmpty(className))
            {
                throw new ArgumentNullException("实例对应的表不能为空。");
            }
            return className;
        }
        /// <summary>
        /// 获取视图名，读取用
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        private string GetViewName(Type type)
        {
            // 视图
            String extendName = GetPersistenceAttribute(type).ExtendName;
            if (string.IsNullOrEmpty(extendName))
            {
                // 不定义视图则获取表
                return GetTableName(type);
            }
            return extendName;
        }
        #endregion

        /// <summary>
        /// 将实体持久化到数据库
        /// </summary>
        /// <param name="t"></param>
        /// <returns>数据库记录主键</returns>
        public virtual string Add(T t)
        {
            // 数据库字段
            String fields = string.Empty;
            // 实体属性值
            String values = string.Empty;
            // 表名
            String className = GetTableName(typeof(T));
            // 所有属性
            Type type = t.GetType();
            PropertyInfo[] proList = type.GetProperties();
            // 需要持久化到数据库的字段个数
            int dbColumnCount = 0;
            IList<SqlParameter> paramList = new List<SqlParameter>();
            foreach (PropertyInfo item in proList)
            {
                PersistenceAttribute pAttr = GetPersistenceAttribute(item);
                // 需要持久化的字段，非扩展字段才写入数据库
                if (string.IsNullOrEmpty(pAttr.ColumnName) == false && pAttr.IsExtend == false)
                {
                    if (pAttr.IsPrimaryKey == true)
                        continue;
                    string colName = pAttr.ColumnName;
                    object oValue = item.GetValue(t, null);
                    string colValue = (oValue == null) ? null : oValue.ToString();
                    if (item.PropertyType == typeof(DateTime))
                    {
                        DateTime tValue = Convert.ToDateTime(item.GetValue(t, null));
                        if (tValue == DateTime.MaxValue || tValue == DateTime.MinValue)
                        {
                            colValue = null;
                        }
                    }
                    // 参数值
                    SqlParameter param = new SqlParameter("@" + colName, colValue);
                    // 构造带参数的sql
                    fields += colName + ",";
                    values += "@" + colName + ",";
                    paramList.Add(param);
                    dbColumnCount++;
                }
            }
            // 构造带参数sql
            fields = fields.Substring(0, fields.Length - 1);
            values = values.Substring(0, values.Length - 1);
            String sql = "INSERT INTO [{0}] ( {1} ) VALUES ( {2} ) SELECT @@IDENTITY";
            sql = String.Format(sql, className, fields, values);
            // 参数数组
            SqlParameter[] paramArray = new SqlParameter[paramList.Count];
            paramList.CopyTo(paramArray, 0);
            return Convert.ToString(SqlHelper.ExecuteScalar(this.ConnectionString, CommandType.Text, sql, paramArray));
        }
        /// <summary>
        /// 根据实体主键的值，在数据库中删除对饮记录
        /// </summary>
        /// <param name="t"></param>
        public virtual void Delete(T t)
        {
            String columnName = string.Empty;
            String id = string.Empty;
            Type type = t.GetType();
            // 表名
            String className = GetTableName(typeof(T));

            IList<SqlParameter> paramList = new List<SqlParameter>();
            PropertyInfo[] proList = type.GetProperties();
            foreach (PropertyInfo item in proList)
            {
                PersistenceAttribute pAttr = GetPersistenceAttribute(item);
                // 需要持久化的字段才写入数据库
                if (string.IsNullOrEmpty(pAttr.ColumnName) == false)
                {
                    if (pAttr.IsPrimaryKey == true)
                    {
                        id = item.GetValue(t, null).ToString();
                        columnName = pAttr.ColumnName;
                        SqlParameter param = new SqlParameter("@" + columnName, id);
                        paramList.Add(param);
                        break;
                    }
                }
            }
            String sql = "DELETE [{0}] WHERE {1} = @{2}";
            sql = String.Format(sql, className, columnName, columnName);
            // 参数数组
            SqlParameter[] paramArray = new SqlParameter[paramList.Count];
            paramList.CopyTo(paramArray, 0);
            SqlHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, sql, paramArray);
        }
        /// <summary>
        /// 将实体的所有主键，更新到数据库中的所有字段
        /// </summary>
        /// <param name="t"></param>
        public virtual void Update(T t)
        {
            String pkColumnName = string.Empty;
            String id = string.Empty;
            String content = string.Empty;
            string where = string.Empty;
            // 表名
            String className = GetTableName(typeof(T));

            IList<SqlParameter> paramList = new List<SqlParameter>();
            Type type = t.GetType();
            PropertyInfo[] proList = type.GetProperties();
            foreach (PropertyInfo item in proList)
            {
                PersistenceAttribute pAttr = GetPersistenceAttribute(item);
                // 需要持久化的字段才写入数据库
                if (string.IsNullOrEmpty(pAttr.ColumnName) == false && pAttr.IsExtend == false)
                {
                    string colName = pAttr.ColumnName;
                    if (pAttr.IsPrimaryKey == true)
                    {
                        where += string.Format(" {0} = @{0} AND", colName);
                    }
                    else
                    {
                        content += string.Format("{0} = @{0} ,", colName);
                    }
                    object oValue = item.GetValue(t, null);
                    string colValue = (oValue == null) ? null : oValue.ToString();
                    if (item.PropertyType == typeof(DateTime))
                    {
                        DateTime tValue = Convert.ToDateTime(item.GetValue(t, null));
                        if (tValue == DateTime.MaxValue || tValue == DateTime.MinValue)
                        {
                            colValue = null;
                        }
                    }
                    // 参数值
                    SqlParameter param = new SqlParameter("@" + colName, colValue);
                    paramList.Add(param);
                }
            }
            where = where.Remove(where.Length - 4);
            content = content.Substring(0, content.Length - 1);
            String sql = "UPDATE [{0}] SET {1} WHERE {2}";
            sql = String.Format(sql, className, content, where);
            // 参数数组
            SqlParameter[] paramArray = new SqlParameter[paramList.Count];
            paramList.CopyTo(paramArray, 0);
            SqlHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, sql, paramArray);
        }
    }
}
